USE [QLDL]
GO

/****** Object:  StoredProcedure [dbo].[sp_BaoCaoDoanhSo]    Script Date: 01/03/2011 15:27:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_BaoCaoDoanhSo]
	@Thang int, @Nam int
as
begin
	declare @TongTienThang money
	select @TongTienThang = SUM(TongTien) from PHIEUXUATHANG 
									where (MONTH(NgayLap) = @Thang) and (YEAR(NgayLap) = @Nam)
	if (@TongTienThang is null)
		return 1
		
	create table #TEMP_DOANHSO(Thang int, Nam int, MaDaiLy nvarchar(10), SoLuongPXH int, TongTriGia money, TyLe float)
	insert into #TEMP_DOANHSO(Thang, Nam, MaDaiLy, SoLuongPXH, TongTriGia, TyLe)
				select @Thang, @Nam, DAILY.MaDaiLy, COUNT(MaPhieuXuat), SUM(TongTien), SUM(TongTien) / @TongTienThang
				from DAILY, PHIEUXUATHANG
				where (DAILY.MaDaiLy = PHIEUXUATHANG.MaDaiLy) and (MONTH(NgayLap) = @Thang) and (YEAR(NgayLap) = @Nam)
				group by DAILY.MaDaiLy, MONTH(NgayLap), YEAR(NgayLap)
				
	delete from DOANHSO where (Thang = @Thang) and (Nam = @Nam)
		
	insert into DOANHSO(Thang, Nam, MaDaiLy, SoLuongPXH, TongTriGia, TyLe)
				select * from #TEMP_DOANHSO
				
	return @TongTienThang
end


GO


